{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Index Alignment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option('max_columns', 4, 'max_rows', 10, 'max_colwidth', 12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examining the Index object"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['INSTNM', 'CITY', 'STABBR', 'HBCU', 'MENONLY', 'WOMENONLY', 'RELAFFIL',\n",
       "       'SATVRMID', 'SATMTMID', 'DISTANCEONLY', 'UGDS', 'UGDS_WHITE',\n",
       "       'UGDS_BLACK', 'UGDS_HISP', 'UGDS_ASIAN', 'UGDS_AIAN', 'UGDS_NHPI',\n",
       "       'UGDS_2MOR', 'UGDS_NRA', 'UGDS_UNKN', 'PPTUG_EF', 'CURROPER', 'PCTPELL',\n",
       "       'PCTFLOAN', 'UG25ABV', 'MD_EARN_WNE_P10', 'GRAD_DEBT_MDN_SUPP'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college = pd.read_csv('data/college.csv')\n",
    "columns = college.columns\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['INSTNM', 'CITY', 'STABBR', 'HBCU', 'MENONLY', 'WOMENONLY',\n",
       "       'RELAFFIL', 'SATVRMID', 'SATMTMID', 'DISTANCEONLY', 'UGDS',\n",
       "       'UGDS_WHITE', 'UGDS_BLACK', 'UGDS_HISP', 'UGDS_ASIAN', 'UGDS_AIAN',\n",
       "       'UGDS_NHPI', 'UGDS_2MOR', 'UGDS_NRA', 'UGDS_UNKN', 'PPTUG_EF',\n",
       "       'CURROPER', 'PCTPELL', 'PCTFLOAN', 'UG25ABV', 'MD_EARN_WNE_P10',\n",
       "       'GRAD_DEBT_MDN_SUPP'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'WOMENONLY'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns[5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CITY', 'SATMTMID', 'UGDS'], dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns[[1,8,10]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['PPTUG_EF', 'CURROPER', 'PCTPELL'], dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns[-7:-4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('CITY', 'WOMENONLY', 0)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns.min(), columns.max(), columns.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['INSTNM_A', 'CITY_A', 'STABBR_A', 'HBCU_A', 'MENONLY_A', 'WOMENONLY_A',\n",
       "       'RELAFFIL_A', 'SATVRMID_A', 'SATMTMID_A', 'DISTANCEONLY_A', 'UGDS_A',\n",
       "       'UGDS_WHITE_A', 'UGDS_BLACK_A', 'UGDS_HISP_A', 'UGDS_ASIAN_A',\n",
       "       'UGDS_AIAN_A', 'UGDS_NHPI_A', 'UGDS_2MOR_A', 'UGDS_NRA_A',\n",
       "       'UGDS_UNKN_A', 'PPTUG_EF_A', 'CURROPER_A', 'PCTPELL_A', 'PCTFLOAN_A',\n",
       "       'UG25ABV_A', 'MD_EARN_WNE_P10_A', 'GRAD_DEBT_MDN_SUPP_A'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns + '_A'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ True, False,  True,  True,  True,  True,  True,  True,  True,\n",
       "       False,  True,  True,  True,  True,  True,  True,  True,  True,\n",
       "        True,  True,  True, False,  True,  True,  True,  True,  True])"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns > 'G'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "Index does not support mutable operations",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-20-0697dab364a3>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mcolumns\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'city'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36m__setitem__\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   4258\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4259\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__setitem__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4260\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0mTypeError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Index does not support mutable operations\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   4261\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4262\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__getitem__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mTypeError\u001b[0m: Index does not support mutable operations"
     ]
    }
   ],
   "source": [
    "columns[1] = 'city'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There's more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['INSTNM', 'CITY', 'STABBR', 'HBCU'], dtype='object')"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1 = columns[:4]\n",
    "c1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['STABBR', 'HBCU', 'MENONLY', 'WOMENONLY'], dtype='object')"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c2 = columns[2:6]\n",
    "c2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CITY', 'HBCU', 'INSTNM', 'MENONLY', 'STABBR', 'WOMENONLY'], dtype='object')"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1.union(c2) # or `c1 | c2`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['CITY', 'INSTNM', 'MENONLY', 'WOMENONLY'], dtype='object')"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1.symmetric_difference(c2) # or `c1 ^ c2`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Producing Cartesian products"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    0\n",
       "a    1\n",
       "a    2\n",
       "b    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1 = pd.Series(index=list('aaab'), data=np.arange(4))\n",
    "s1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "c    0\n",
       "a    1\n",
       "b    2\n",
       "a    3\n",
       "b    4\n",
       "b    5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s2 = pd.Series(index=list('cababb'), data=np.arange(6))\n",
    "s2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1.0\n",
       "a    3.0\n",
       "a    2.0\n",
       "a    4.0\n",
       "a    3.0\n",
       "a    5.0\n",
       "b    5.0\n",
       "b    7.0\n",
       "b    8.0\n",
       "c    NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1 + s2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There's more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    0\n",
       "a    2\n",
       "a    4\n",
       "b    6\n",
       "b    8\n",
       "dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1 = pd.Series(index=list('aaabb'), data=np.arange(5))\n",
    "s2 = pd.Series(index=list('aaabb'), data=np.arange(5))\n",
    "s1 + s2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    2\n",
       "a    3\n",
       "a    4\n",
       "a    3\n",
       "a    4\n",
       "    ..\n",
       "a    6\n",
       "b    3\n",
       "b    4\n",
       "b    4\n",
       "b    5\n",
       "Length: 13, dtype: int64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1 = pd.Series(index=list('aaabb'), data=np.arange(5))\n",
    "s2 = pd.Series(index=list('bbaaa'), data=np.arange(5))\n",
    "s1 + s2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s3 = pd.Series(index=list('ab'), data=np.arange(2))\n",
    "s4 = pd.Series(index=list('ba'), data=np.arange(2))\n",
    "s3 + s4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploding indexes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "      <th>POSITION_TITLE</th>\n",
       "      <th>...</th>\n",
       "      <th>HIRE_DATE</th>\n",
       "      <th>JOB_DATE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RACE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Hispanic/Latino</th>\n",
       "      <td>0</td>\n",
       "      <td>ASSISTAN...</td>\n",
       "      <td>...</td>\n",
       "      <td>2006-06-12</td>\n",
       "      <td>2012-10-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hispanic/Latino</th>\n",
       "      <td>1</td>\n",
       "      <td>LIBRARY ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2000-07-19</td>\n",
       "      <td>2010-09-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>White</th>\n",
       "      <td>2</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-02-03</td>\n",
       "      <td>2015-02-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>White</th>\n",
       "      <td>3</td>\n",
       "      <td>ENGINEER...</td>\n",
       "      <td>...</td>\n",
       "      <td>1982-02-08</td>\n",
       "      <td>1991-05-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>White</th>\n",
       "      <td>4</td>\n",
       "      <td>ELECTRICIAN</td>\n",
       "      <td>...</td>\n",
       "      <td>1989-06-19</td>\n",
       "      <td>1994-10-22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              UNIQUE_ID POSITION_TITLE  ...   HIRE_DATE    JOB_DATE\n",
       "RACE                                    ...                        \n",
       "Hispanic/...          0  ASSISTAN...    ...  2006-06-12  2012-10-13\n",
       "Hispanic/...          1  LIBRARY ...    ...  2000-07-19  2010-09-18\n",
       "White                 2  POLICE O...    ...  2015-02-03  2015-02-03\n",
       "White                 3  ENGINEER...    ...  1982-02-08  1991-05-25\n",
       "White                 4  ELECTRICIAN    ...  1989-06-19  1994-10-22\n",
       "\n",
       "[5 rows x 9 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee = pd.read_csv('data/employee.csv', index_col='RACE')\n",
    "employee.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary1 = employee['BASE_SALARY']\n",
    "salary2 = employee['BASE_SALARY']\n",
    "salary1 is salary2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary2 = employee['BASE_SALARY'].copy()\n",
    "salary1 is salary2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RACE\n",
       "American Indian or Alaskan Native    78355.0\n",
       "American Indian or Alaskan Native    26125.0\n",
       "American Indian or Alaskan Native    98536.0\n",
       "American Indian or Alaskan Native        NaN\n",
       "American Indian or Alaskan Native    55461.0\n",
       "Name: BASE_SALARY, dtype: float64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary1 = salary1.sort_index()\n",
    "salary1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RACE\n",
       "Hispanic/Latino    121862.0\n",
       "Hispanic/Latino     26125.0\n",
       "White               45279.0\n",
       "White               63166.0\n",
       "White               56347.0\n",
       "Name: BASE_SALARY, dtype: float64"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "salary_add = salary1 + salary2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RACE\n",
       "American Indian or Alaskan Native    138702.0\n",
       "American Indian or Alaskan Native    156710.0\n",
       "American Indian or Alaskan Native    176891.0\n",
       "American Indian or Alaskan Native    159594.0\n",
       "American Indian or Alaskan Native    127734.0\n",
       "Name: BASE_SALARY, dtype: float64"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_add.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2000, 2000, 1175424, 2000)"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_add1 = salary1 + salary1\n",
    "len(salary1), len(salary2), len(salary_add), len(salary_add1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There's more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Black or African American            700\n",
       "White                                665\n",
       "Hispanic/Latino                      480\n",
       "Asian/Pacific Islander               107\n",
       "NaN                                   35\n",
       "American Indian or Alaskan Native     11\n",
       "Others                                 2\n",
       "Name: RACE, dtype: int64"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index_vc = salary1.index.value_counts(dropna=False)\n",
    "index_vc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1175424"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index_vc.pow(2).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filling values with unequal indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yearID</th>\n",
       "      <th>stint</th>\n",
       "      <th>...</th>\n",
       "      <th>SF</th>\n",
       "      <th>GIDP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>playerID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>altuvjo01</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cartech02</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>castrja01</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>corpoca01</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dominma01</th>\n",
       "      <td>2014</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>23.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           yearID  stint  ...   SF  GIDP\n",
       "playerID                  ...           \n",
       "altuvjo01    2014      1  ...  5.0  20.0\n",
       "cartech02    2014      1  ...  4.0  12.0\n",
       "castrja01    2014      1  ...  3.0  11.0\n",
       "corpoca01    2014      1  ...  2.0   3.0\n",
       "dominma01    2014      1  ...  7.0  23.0\n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baseball_14 = pd.read_csv('data/baseball14.csv',\n",
    "   index_col='playerID')\n",
    "baseball_15 = pd.read_csv('data/baseball15.csv',\n",
    "   index_col='playerID')\n",
    "baseball_16 = pd.read_csv('data/baseball16.csv',\n",
    "   index_col='playerID')\n",
    "baseball_14.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['corpoca01', 'dominma01', 'fowlede01', 'grossro01', 'guzmaje01',\n",
       "       'hoeslj01', 'krausma01', 'preslal01', 'singljo02'],\n",
       "      dtype='object', name='playerID')"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baseball_14.index.difference(baseball_15.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['cartech02', 'corpoca01', 'dominma01', 'fowlede01', 'grossro01',\n",
       "       'guzmaje01', 'hoeslj01', 'krausma01', 'preslal01', 'singljo02',\n",
       "       'villajo01'],\n",
       "      dtype='object', name='playerID')"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baseball_14.index.difference(baseball_16.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "playerID\n",
       "altuvjo01    225\n",
       "cartech02    115\n",
       "castrja01    103\n",
       "corpoca01     40\n",
       "dominma01    121\n",
       "Name: H, dtype: int64"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hits_14 = baseball_14['H']\n",
    "hits_15 = baseball_15['H']\n",
    "hits_16 = baseball_16['H']\n",
    "hits_14.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "playerID\n",
       "altuvjo01    425.0\n",
       "cartech02    193.0\n",
       "castrja01    174.0\n",
       "congeha01      NaN\n",
       "corpoca01      NaN\n",
       "Name: H, dtype: float64"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(hits_14 + hits_15).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "playerID\n",
       "altuvjo01    425.0\n",
       "cartech02    193.0\n",
       "castrja01    174.0\n",
       "congeha01     46.0\n",
       "corpoca01     40.0\n",
       "Name: H, dtype: float64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hits_14.add(hits_15, fill_value=0).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "playerID\n",
       "altuvjo01    641.0\n",
       "bregmal01     53.0\n",
       "cartech02    193.0\n",
       "castrja01    243.0\n",
       "congeha01     46.0\n",
       "Name: H, dtype: float64"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hits_total = (hits_14\n",
    "   .add(hits_15, fill_value=0)\n",
    "   .add(hits_16, fill_value=0)\n",
    ")\n",
    "hits_total.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hits_total.hasnans"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    NaN\n",
       "b    3.0\n",
       "c    NaN\n",
       "d    1.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series(index=['a', 'b', 'c', 'd'],\n",
    "              data=[np.nan, 3, np.nan, 1])\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a     NaN\n",
       "b     6.0\n",
       "c    10.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1 = pd.Series(index=['a', 'b', 'c'], data=[np.nan, 6, 10])\n",
    "s1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a     NaN\n",
       "b     9.0\n",
       "c    15.0\n",
       "d     6.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.add(s1, fill_value=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There's more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>G</th>\n",
       "      <th>AB</th>\n",
       "      <th>R</th>\n",
       "      <th>H</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>playerID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>altuvjo01</th>\n",
       "      <td>158</td>\n",
       "      <td>660</td>\n",
       "      <td>85</td>\n",
       "      <td>225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cartech02</th>\n",
       "      <td>145</td>\n",
       "      <td>507</td>\n",
       "      <td>68</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>castrja01</th>\n",
       "      <td>126</td>\n",
       "      <td>465</td>\n",
       "      <td>43</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>corpoca01</th>\n",
       "      <td>55</td>\n",
       "      <td>170</td>\n",
       "      <td>22</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dominma01</th>\n",
       "      <td>157</td>\n",
       "      <td>564</td>\n",
       "      <td>51</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             G   AB   R    H\n",
       "playerID                    \n",
       "altuvjo01  158  660  85  225\n",
       "cartech02  145  507  68  115\n",
       "castrja01  126  465  43  103\n",
       "corpoca01   55  170  22   40\n",
       "dominma01  157  564  51  121"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_14 = baseball_14[['G','AB', 'R', 'H']]\n",
    "df_14.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AB</th>\n",
       "      <th>R</th>\n",
       "      <th>H</th>\n",
       "      <th>HR</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>playerID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>altuvjo01</th>\n",
       "      <td>638</td>\n",
       "      <td>86</td>\n",
       "      <td>200</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cartech02</th>\n",
       "      <td>391</td>\n",
       "      <td>50</td>\n",
       "      <td>78</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>castrja01</th>\n",
       "      <td>337</td>\n",
       "      <td>38</td>\n",
       "      <td>71</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>congeha01</th>\n",
       "      <td>201</td>\n",
       "      <td>25</td>\n",
       "      <td>46</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>correca01</th>\n",
       "      <td>387</td>\n",
       "      <td>52</td>\n",
       "      <td>108</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            AB   R    H  HR\n",
       "playerID                   \n",
       "altuvjo01  638  86  200  15\n",
       "cartech02  391  50   78  24\n",
       "castrja01  337  38   71  11\n",
       "congeha01  201  25   46  11\n",
       "correca01  387  52  108  22"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_15 = baseball_15[['AB', 'R', 'H', 'HR']]\n",
    "df_15.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow0_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow0_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow1_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow1_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow2_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow2_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow3_col0 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow3_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow3_col2 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow3_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow3_col4 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow4_col0 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow4_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow4_col2 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow4_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow4_col4 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow5_col0 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow5_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow5_col2 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow5_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow5_col4 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow6_col0 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow6_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow6_col2 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow6_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow6_col4 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow7_col0 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow7_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow7_col2 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow7_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow7_col4 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow8_col0 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow8_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow8_col2 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow8_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow8_col4 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow9_col0 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow9_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow9_col2 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow9_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_5058d610_2208_11ea_b2b6_a45e60ecc33frow9_col4 {\n",
       "            background-color:  yellow;\n",
       "        }</style><table id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33f\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >AB</th>        <th class=\"col_heading level0 col1\" >G</th>        <th class=\"col_heading level0 col2\" >H</th>        <th class=\"col_heading level0 col3\" >HR</th>        <th class=\"col_heading level0 col4\" >R</th>    </tr>    <tr>        <th class=\"index_name level0\" >playerID</th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33flevel0_row0\" class=\"row_heading level0 row0\" >altuvjo01</th>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow0_col0\" class=\"data row0 col0\" >1298</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow0_col1\" class=\"data row0 col1\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow0_col2\" class=\"data row0 col2\" >425</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow0_col3\" class=\"data row0 col3\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow0_col4\" class=\"data row0 col4\" >171</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33flevel0_row1\" class=\"row_heading level0 row1\" >cartech02</th>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow1_col0\" class=\"data row1 col0\" >898</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow1_col1\" class=\"data row1 col1\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow1_col2\" class=\"data row1 col2\" >193</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow1_col3\" class=\"data row1 col3\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow1_col4\" class=\"data row1 col4\" >118</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33flevel0_row2\" class=\"row_heading level0 row2\" >castrja01</th>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow2_col0\" class=\"data row2 col0\" >802</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow2_col1\" class=\"data row2 col1\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow2_col2\" class=\"data row2 col2\" >174</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow2_col3\" class=\"data row2 col3\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow2_col4\" class=\"data row2 col4\" >81</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33flevel0_row3\" class=\"row_heading level0 row3\" >congeha01</th>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow3_col0\" class=\"data row3 col0\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow3_col1\" class=\"data row3 col1\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow3_col2\" class=\"data row3 col2\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow3_col3\" class=\"data row3 col3\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow3_col4\" class=\"data row3 col4\" >nan</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33flevel0_row4\" class=\"row_heading level0 row4\" >corpoca01</th>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow4_col0\" class=\"data row4 col0\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow4_col1\" class=\"data row4 col1\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow4_col2\" class=\"data row4 col2\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow4_col3\" class=\"data row4 col3\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow4_col4\" class=\"data row4 col4\" >nan</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33flevel0_row5\" class=\"row_heading level0 row5\" >correca01</th>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow5_col0\" class=\"data row5 col0\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow5_col1\" class=\"data row5 col1\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow5_col2\" class=\"data row5 col2\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow5_col3\" class=\"data row5 col3\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow5_col4\" class=\"data row5 col4\" >nan</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33flevel0_row6\" class=\"row_heading level0 row6\" >dominma01</th>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow6_col0\" class=\"data row6 col0\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow6_col1\" class=\"data row6 col1\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow6_col2\" class=\"data row6 col2\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow6_col3\" class=\"data row6 col3\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow6_col4\" class=\"data row6 col4\" >nan</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33flevel0_row7\" class=\"row_heading level0 row7\" >fowlede01</th>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow7_col0\" class=\"data row7 col0\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow7_col1\" class=\"data row7 col1\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow7_col2\" class=\"data row7 col2\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow7_col3\" class=\"data row7 col3\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow7_col4\" class=\"data row7 col4\" >nan</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33flevel0_row8\" class=\"row_heading level0 row8\" >gattiev01</th>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow8_col0\" class=\"data row8 col0\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow8_col1\" class=\"data row8 col1\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow8_col2\" class=\"data row8 col2\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow8_col3\" class=\"data row8 col3\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow8_col4\" class=\"data row8 col4\" >nan</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33flevel0_row9\" class=\"row_heading level0 row9\" >gomezca01</th>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow9_col0\" class=\"data row9 col0\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow9_col1\" class=\"data row9 col1\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow9_col2\" class=\"data row9 col2\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow9_col3\" class=\"data row9 col3\" >nan</td>\n",
       "                        <td id=\"T_5058d610_2208_11ea_b2b6_a45e60ecc33frow9_col4\" class=\"data row9 col4\" >nan</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x10ac26da0>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df_14 + df_15).head(10).style.highlight_null('yellow')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "    #T_59cd2836_2208_11ea_9a59_a45e60ecc33frow3_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_59cd2836_2208_11ea_9a59_a45e60ecc33frow4_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_59cd2836_2208_11ea_9a59_a45e60ecc33frow5_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_59cd2836_2208_11ea_9a59_a45e60ecc33frow6_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_59cd2836_2208_11ea_9a59_a45e60ecc33frow7_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_59cd2836_2208_11ea_9a59_a45e60ecc33frow8_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_59cd2836_2208_11ea_9a59_a45e60ecc33frow9_col1 {\n",
       "            background-color:  yellow;\n",
       "        }</style><table id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33f\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >AB</th>        <th class=\"col_heading level0 col1\" >G</th>        <th class=\"col_heading level0 col2\" >H</th>        <th class=\"col_heading level0 col3\" >HR</th>        <th class=\"col_heading level0 col4\" >R</th>    </tr>    <tr>        <th class=\"index_name level0\" >playerID</th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33flevel0_row0\" class=\"row_heading level0 row0\" >altuvjo01</th>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow0_col0\" class=\"data row0 col0\" >1298</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow0_col1\" class=\"data row0 col1\" >158</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow0_col2\" class=\"data row0 col2\" >425</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow0_col3\" class=\"data row0 col3\" >15</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow0_col4\" class=\"data row0 col4\" >171</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33flevel0_row1\" class=\"row_heading level0 row1\" >cartech02</th>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow1_col0\" class=\"data row1 col0\" >898</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow1_col1\" class=\"data row1 col1\" >145</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow1_col2\" class=\"data row1 col2\" >193</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow1_col3\" class=\"data row1 col3\" >24</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow1_col4\" class=\"data row1 col4\" >118</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33flevel0_row2\" class=\"row_heading level0 row2\" >castrja01</th>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow2_col0\" class=\"data row2 col0\" >802</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow2_col1\" class=\"data row2 col1\" >126</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow2_col2\" class=\"data row2 col2\" >174</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow2_col3\" class=\"data row2 col3\" >11</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow2_col4\" class=\"data row2 col4\" >81</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33flevel0_row3\" class=\"row_heading level0 row3\" >congeha01</th>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow3_col0\" class=\"data row3 col0\" >201</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow3_col1\" class=\"data row3 col1\" >nan</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow3_col2\" class=\"data row3 col2\" >46</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow3_col3\" class=\"data row3 col3\" >11</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow3_col4\" class=\"data row3 col4\" >25</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33flevel0_row4\" class=\"row_heading level0 row4\" >corpoca01</th>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow4_col0\" class=\"data row4 col0\" >170</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow4_col1\" class=\"data row4 col1\" >55</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow4_col2\" class=\"data row4 col2\" >40</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow4_col3\" class=\"data row4 col3\" >nan</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow4_col4\" class=\"data row4 col4\" >22</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33flevel0_row5\" class=\"row_heading level0 row5\" >correca01</th>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow5_col0\" class=\"data row5 col0\" >387</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow5_col1\" class=\"data row5 col1\" >nan</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow5_col2\" class=\"data row5 col2\" >108</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow5_col3\" class=\"data row5 col3\" >22</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow5_col4\" class=\"data row5 col4\" >52</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33flevel0_row6\" class=\"row_heading level0 row6\" >dominma01</th>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow6_col0\" class=\"data row6 col0\" >564</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow6_col1\" class=\"data row6 col1\" >157</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow6_col2\" class=\"data row6 col2\" >121</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow6_col3\" class=\"data row6 col3\" >nan</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow6_col4\" class=\"data row6 col4\" >51</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33flevel0_row7\" class=\"row_heading level0 row7\" >fowlede01</th>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow7_col0\" class=\"data row7 col0\" >434</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow7_col1\" class=\"data row7 col1\" >116</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow7_col2\" class=\"data row7 col2\" >120</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow7_col3\" class=\"data row7 col3\" >nan</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow7_col4\" class=\"data row7 col4\" >61</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33flevel0_row8\" class=\"row_heading level0 row8\" >gattiev01</th>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow8_col0\" class=\"data row8 col0\" >566</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow8_col1\" class=\"data row8 col1\" >nan</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow8_col2\" class=\"data row8 col2\" >139</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow8_col3\" class=\"data row8 col3\" >27</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow8_col4\" class=\"data row8 col4\" >66</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33flevel0_row9\" class=\"row_heading level0 row9\" >gomezca01</th>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow9_col0\" class=\"data row9 col0\" >149</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow9_col1\" class=\"data row9 col1\" >nan</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow9_col2\" class=\"data row9 col2\" >36</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow9_col3\" class=\"data row9 col3\" >4</td>\n",
       "                        <td id=\"T_59cd2836_2208_11ea_9a59_a45e60ecc33frow9_col4\" class=\"data row9 col4\" >19</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x114bae588>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df_14\n",
    ".add(df_15, fill_value=0)\n",
    ".head(10)\n",
    ".style.highlight_null('yellow')\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding columns from different DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "employee = pd.read_csv('data/employee.csv')\n",
    "dept_sal = employee[['DEPARTMENT', 'BASE_SALARY']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "dept_sal = dept_sal.sort_values(['DEPARTMENT', 'BASE_SALARY'],\n",
    "    ascending=[True, False])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th>BASE_SALARY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1494</th>\n",
       "      <td>Admn. &amp; ...</td>\n",
       "      <td>140416.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>City Con...</td>\n",
       "      <td>64251.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>236</th>\n",
       "      <td>City Cou...</td>\n",
       "      <td>100000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>647</th>\n",
       "      <td>Conventi...</td>\n",
       "      <td>38397.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1500</th>\n",
       "      <td>Dept of ...</td>\n",
       "      <td>89221.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       DEPARTMENT  BASE_SALARY\n",
       "1494  Admn. & ...     140416.0\n",
       "149   City Con...      64251.0\n",
       "236   City Cou...     100000.0\n",
       "647   Conventi...      38397.0\n",
       "1500  Dept of ...      89221.0"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max_dept_sal = dept_sal.drop_duplicates(subset='DEPARTMENT')\n",
    "max_dept_sal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "max_dept_sal = max_dept_sal.set_index('DEPARTMENT')\n",
    "employee = employee.set_index('DEPARTMENT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "      <th>POSITION_TITLE</th>\n",
       "      <th>...</th>\n",
       "      <th>JOB_DATE</th>\n",
       "      <th>MAX_DEPT_SALARY</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Municipal Courts Department</th>\n",
       "      <td>0</td>\n",
       "      <td>ASSISTAN...</td>\n",
       "      <td>...</td>\n",
       "      <td>2012-10-13</td>\n",
       "      <td>121862.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Library</th>\n",
       "      <td>1</td>\n",
       "      <td>LIBRARY ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2010-09-18</td>\n",
       "      <td>107763.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>2</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-02-03</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Fire Department (HFD)</th>\n",
       "      <td>3</td>\n",
       "      <td>ENGINEER...</td>\n",
       "      <td>...</td>\n",
       "      <td>1991-05-25</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>General Services Department</th>\n",
       "      <td>4</td>\n",
       "      <td>ELECTRICIAN</td>\n",
       "      <td>...</td>\n",
       "      <td>1994-10-22</td>\n",
       "      <td>89194.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>1995</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-06-09</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Fire Department (HFD)</th>\n",
       "      <td>1996</td>\n",
       "      <td>COMMUNIC...</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-10-06</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>1997</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-10-13</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>1998</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2011-07-02</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Fire Department (HFD)</th>\n",
       "      <td>1999</td>\n",
       "      <td>FIRE FIG...</td>\n",
       "      <td>...</td>\n",
       "      <td>2010-07-12</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2000 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              UNIQUE_ID POSITION_TITLE  ...    JOB_DATE MAX_DEPT_SALARY\n",
       "DEPARTMENT                              ...                            \n",
       "Municipal...          0  ASSISTAN...    ...  2012-10-13     121862.0   \n",
       "Library               1  LIBRARY ...    ...  2010-09-18     107763.0   \n",
       "Houston P...          2  POLICE O...    ...  2015-02-03     199596.0   \n",
       "Houston F...          3  ENGINEER...    ...  1991-05-25     210588.0   \n",
       "General S...          4  ELECTRICIAN    ...  1994-10-22      89194.0   \n",
       "...                 ...          ...    ...         ...          ...   \n",
       "Houston P...       1995  POLICE O...    ...  2015-06-09     199596.0   \n",
       "Houston F...       1996  COMMUNIC...    ...  2013-10-06     210588.0   \n",
       "Houston P...       1997  POLICE O...    ...  2015-10-13     199596.0   \n",
       "Houston P...       1998  POLICE O...    ...  2011-07-02     199596.0   \n",
       "Houston F...       1999  FIRE FIG...    ...  2010-07-12     210588.0   \n",
       "\n",
       "[2000 rows x 10 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee = (employee\n",
    "   .assign(MAX_DEPT_SALARY=max_dept_sal['BASE_SALARY'])\n",
    ")\n",
    "employee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "      <th>POSITION_TITLE</th>\n",
       "      <th>...</th>\n",
       "      <th>JOB_DATE</th>\n",
       "      <th>MAX_DEPT_SALARY</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [UNIQUE_ID, POSITION_TITLE, BASE_SALARY, RACE, EMPLOYMENT_TYPE, GENDER, EMPLOYMENT_STATUS, HIRE_DATE, JOB_DATE, MAX_DEPT_SALARY]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 10 columns]"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee.query('BASE_SALARY > MAX_DEPT_SALARY')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "employee = pd.read_csv('data/employee.csv')\n",
    "max_dept_sal = (employee\n",
    "    [['DEPARTMENT', 'BASE_SALARY']]\n",
    "    .sort_values(['DEPARTMENT', 'BASE_SALARY'],\n",
    "        ascending=[True, False])\n",
    "    .drop_duplicates(subset='DEPARTMENT')\n",
    "    .set_index('DEPARTMENT')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "      <th>POSITION_TITLE</th>\n",
       "      <th>...</th>\n",
       "      <th>JOB_DATE</th>\n",
       "      <th>MAX_DEPT_SALARY</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Municipal Courts Department</th>\n",
       "      <td>0</td>\n",
       "      <td>ASSISTAN...</td>\n",
       "      <td>...</td>\n",
       "      <td>2012-10-13</td>\n",
       "      <td>121862.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Library</th>\n",
       "      <td>1</td>\n",
       "      <td>LIBRARY ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2010-09-18</td>\n",
       "      <td>107763.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>2</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-02-03</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Fire Department (HFD)</th>\n",
       "      <td>3</td>\n",
       "      <td>ENGINEER...</td>\n",
       "      <td>...</td>\n",
       "      <td>1991-05-25</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>General Services Department</th>\n",
       "      <td>4</td>\n",
       "      <td>ELECTRICIAN</td>\n",
       "      <td>...</td>\n",
       "      <td>1994-10-22</td>\n",
       "      <td>89194.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>1995</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-06-09</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Fire Department (HFD)</th>\n",
       "      <td>1996</td>\n",
       "      <td>COMMUNIC...</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-10-06</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>1997</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-10-13</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>1998</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2011-07-02</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Fire Department (HFD)</th>\n",
       "      <td>1999</td>\n",
       "      <td>FIRE FIG...</td>\n",
       "      <td>...</td>\n",
       "      <td>2010-07-12</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2000 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              UNIQUE_ID POSITION_TITLE  ...    JOB_DATE MAX_DEPT_SALARY\n",
       "DEPARTMENT                              ...                            \n",
       "Municipal...          0  ASSISTAN...    ...  2012-10-13     121862.0   \n",
       "Library               1  LIBRARY ...    ...  2010-09-18     107763.0   \n",
       "Houston P...          2  POLICE O...    ...  2015-02-03     199596.0   \n",
       "Houston F...          3  ENGINEER...    ...  1991-05-25     210588.0   \n",
       "General S...          4  ELECTRICIAN    ...  1994-10-22      89194.0   \n",
       "...                 ...          ...    ...         ...          ...   \n",
       "Houston P...       1995  POLICE O...    ...  2015-06-09     199596.0   \n",
       "Houston F...       1996  COMMUNIC...    ...  2013-10-06     210588.0   \n",
       "Houston P...       1997  POLICE O...    ...  2015-10-13     199596.0   \n",
       "Houston P...       1998  POLICE O...    ...  2011-07-02     199596.0   \n",
       "Houston F...       1999  FIRE FIG...    ...  2010-07-12     210588.0   \n",
       "\n",
       "[2000 rows x 10 columns]"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(employee\n",
    "   .set_index('DEPARTMENT')\n",
    "   .assign(MAX_DEPT_SALARY=max_dept_sal['BASE_SALARY'])\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BASE_SALARY</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Public Works &amp; Engineering-PWE</th>\n",
       "      <td>34861.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Airport System (HAS)</th>\n",
       "      <td>29286.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>31907.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>66614.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>42000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>43443.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>66614.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public Works &amp; Engineering-PWE</th>\n",
       "      <td>52582.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Finance</th>\n",
       "      <td>93168.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Houston Police Department-HPD</th>\n",
       "      <td>35318.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              BASE_SALARY\n",
       "DEPARTMENT               \n",
       "Public Wo...      34861.0\n",
       "Houston A...      29286.0\n",
       "Houston P...      31907.0\n",
       "Houston P...      66614.0\n",
       "Houston P...      42000.0\n",
       "Houston P...      43443.0\n",
       "Houston P...      66614.0\n",
       "Public Wo...      52582.0\n",
       "Finance           93168.0\n",
       "Houston P...      35318.0"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "random_salary = (dept_sal\n",
    "    .sample(n=10, random_state=42)\n",
    "    .set_index('DEPARTMENT')\n",
    ")\n",
    "random_salary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "cannot reindex from a duplicate axis",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-103-2c3f44188e8c>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0memployee\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'RANDOM_SALARY'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrandom_salary\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'BASE_SALARY'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m__setitem__\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   3485\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3486\u001b[0m             \u001b[0;31m# set column\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3487\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3488\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3489\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_setitem_slice\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m_set_item\u001b[0;34m(self, key, value)\u001b[0m\n\u001b[1;32m   3562\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3563\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_ensure_valid_index\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3564\u001b[0;31m         \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sanitize_column\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3565\u001b[0m         \u001b[0mNDFrame\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_item\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3566\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m_sanitize_column\u001b[0;34m(self, key, value, broadcast)\u001b[0m\n\u001b[1;32m   3724\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3725\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mSeries\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3726\u001b[0;31m             \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mreindexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3727\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3728\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mDataFrame\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mreindexer\u001b[0;34m(value)\u001b[0m\n\u001b[1;32m   3715\u001b[0m                     \u001b[0;31m# duplicate axis\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3716\u001b[0m                     \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mis_unique\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3717\u001b[0;31m                         \u001b[0;32mraise\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3718\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3719\u001b[0m                     \u001b[0;31m# other\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mreindexer\u001b[0;34m(value)\u001b[0m\n\u001b[1;32m   3710\u001b[0m                 \u001b[0;31m# GH 4107\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3711\u001b[0m                 \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3712\u001b[0;31m                     \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreindex\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_values\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3713\u001b[0m                 \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3714\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/series.py\u001b[0m in \u001b[0;36mreindex\u001b[0;34m(self, index, **kwargs)\u001b[0m\n\u001b[1;32m   4219\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mAppender\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mgeneric\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mNDFrame\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreindex\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__doc__\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4220\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mreindex\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4221\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0msuper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreindex\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   4222\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4223\u001b[0m     def drop(\n",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36mreindex\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   4512\u001b[0m         \u001b[0;31m# perform the reindex on the axes\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4513\u001b[0m         return self._reindex_axes(\n\u001b[0;32m-> 4514\u001b[0;31m             \u001b[0maxes\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlevel\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlimit\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtolerance\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfill_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   4515\u001b[0m         ).__finalize__(self)\n\u001b[1;32m   4516\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m_reindex_axes\u001b[0;34m(self, axes, level, limit, tolerance, method, fill_value, copy)\u001b[0m\n\u001b[1;32m   4533\u001b[0m                 \u001b[0mfill_value\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mfill_value\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4534\u001b[0m                 \u001b[0mcopy\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4535\u001b[0;31m                 \u001b[0mallow_dups\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   4536\u001b[0m             )\n\u001b[1;32m   4537\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m_reindex_with_indexers\u001b[0;34m(self, reindexers, fill_value, copy, allow_dups)\u001b[0m\n\u001b[1;32m   4575\u001b[0m                 \u001b[0mfill_value\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mfill_value\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4576\u001b[0m                 \u001b[0mallow_dups\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mallow_dups\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4577\u001b[0;31m                 \u001b[0mcopy\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   4578\u001b[0m             )\n\u001b[1;32m   4579\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/internals/managers.py\u001b[0m in \u001b[0;36mreindex_indexer\u001b[0;34m(self, new_axis, indexer, axis, fill_value, allow_dups, copy)\u001b[0m\n\u001b[1;32m   1249\u001b[0m         \u001b[0;31m# some axes don't allow reindexing with dups\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1250\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mallow_dups\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1251\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0maxes\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0maxis\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_can_reindex\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1252\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1253\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0maxis\u001b[0m \u001b[0;34m>=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mndim\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.env/364/lib/python3.6/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36m_can_reindex\u001b[0;34m(self, indexer)\u001b[0m\n\u001b[1;32m   3360\u001b[0m         \u001b[0;31m# trying to reindex on an axis with duplicates\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3361\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mis_unique\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3362\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"cannot reindex from a duplicate axis\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3363\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3364\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mreindex\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtarget\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlevel\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlimit\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtolerance\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mValueError\u001b[0m: cannot reindex from a duplicate axis"
     ]
    }
   ],
   "source": [
    "employee['RANDOM_SALARY'] = random_salary['BASE_SALARY']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There's more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "140416.0    29\n",
       "100000.0    11\n",
       "64251.0      5\n",
       "Name: MAX_SALARY2, dtype: int64"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(employee\n",
    "    .set_index('DEPARTMENT')\n",
    "    .assign(MAX_SALARY2=max_dept_sal['BASE_SALARY'].head(3))\n",
    "    .MAX_SALARY2\n",
    "    .value_counts()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "max_sal = (employee\n",
    "    .groupby('DEPARTMENT')\n",
    "    .BASE_SALARY\n",
    "    .transform('max')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "      <th>POSITION_TITLE</th>\n",
       "      <th>...</th>\n",
       "      <th>JOB_DATE</th>\n",
       "      <th>MAX_DEPT_SALARY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ASSISTAN...</td>\n",
       "      <td>...</td>\n",
       "      <td>2012-10-13</td>\n",
       "      <td>121862.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>LIBRARY ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2010-09-18</td>\n",
       "      <td>107763.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-02-03</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>ENGINEER...</td>\n",
       "      <td>...</td>\n",
       "      <td>1991-05-25</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>ELECTRICIAN</td>\n",
       "      <td>...</td>\n",
       "      <td>1994-10-22</td>\n",
       "      <td>89194.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995</th>\n",
       "      <td>1995</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-06-09</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>1996</td>\n",
       "      <td>COMMUNIC...</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-10-06</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997</th>\n",
       "      <td>1997</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-10-13</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998</th>\n",
       "      <td>1998</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2011-07-02</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>1999</td>\n",
       "      <td>FIRE FIG...</td>\n",
       "      <td>...</td>\n",
       "      <td>2010-07-12</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2000 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      UNIQUE_ID POSITION_TITLE  ...    JOB_DATE  MAX_DEPT_SALARY\n",
       "0             0  ASSISTAN...    ...  2012-10-13     121862.0    \n",
       "1             1  LIBRARY ...    ...  2010-09-18     107763.0    \n",
       "2             2  POLICE O...    ...  2015-02-03     199596.0    \n",
       "3             3  ENGINEER...    ...  1991-05-25     210588.0    \n",
       "4             4  ELECTRICIAN    ...  1994-10-22      89194.0    \n",
       "...         ...          ...    ...         ...          ...    \n",
       "1995       1995  POLICE O...    ...  2015-06-09     199596.0    \n",
       "1996       1996  COMMUNIC...    ...  2013-10-06     210588.0    \n",
       "1997       1997  POLICE O...    ...  2015-10-13     199596.0    \n",
       "1998       1998  POLICE O...    ...  2011-07-02     199596.0    \n",
       "1999       1999  FIRE FIG...    ...  2010-07-12     210588.0    \n",
       "\n",
       "[2000 rows x 11 columns]"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(employee\n",
    "    .assign(MAX_DEPT_SALARY=max_sal)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "max_sal = (employee\n",
    "    .groupby('DEPARTMENT')\n",
    "    .BASE_SALARY\n",
    "    .max()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "      <th>POSITION_TITLE</th>\n",
       "      <th>...</th>\n",
       "      <th>JOB_DATE</th>\n",
       "      <th>MAX_DEPT_SALARY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ASSISTAN...</td>\n",
       "      <td>...</td>\n",
       "      <td>2012-10-13</td>\n",
       "      <td>121862.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>LIBRARY ...</td>\n",
       "      <td>...</td>\n",
       "      <td>2010-09-18</td>\n",
       "      <td>107763.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-02-03</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>ENGINEER...</td>\n",
       "      <td>...</td>\n",
       "      <td>1991-05-25</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>ELECTRICIAN</td>\n",
       "      <td>...</td>\n",
       "      <td>1994-10-22</td>\n",
       "      <td>89194.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995</th>\n",
       "      <td>1995</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-06-09</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996</th>\n",
       "      <td>1996</td>\n",
       "      <td>COMMUNIC...</td>\n",
       "      <td>...</td>\n",
       "      <td>2013-10-06</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997</th>\n",
       "      <td>1997</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2015-10-13</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998</th>\n",
       "      <td>1998</td>\n",
       "      <td>POLICE O...</td>\n",
       "      <td>...</td>\n",
       "      <td>2011-07-02</td>\n",
       "      <td>199596.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>1999</td>\n",
       "      <td>FIRE FIG...</td>\n",
       "      <td>...</td>\n",
       "      <td>2010-07-12</td>\n",
       "      <td>210588.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2000 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      UNIQUE_ID POSITION_TITLE  ...    JOB_DATE  MAX_DEPT_SALARY\n",
       "0             0  ASSISTAN...    ...  2012-10-13     121862.0    \n",
       "1             1  LIBRARY ...    ...  2010-09-18     107763.0    \n",
       "2             2  POLICE O...    ...  2015-02-03     199596.0    \n",
       "3             3  ENGINEER...    ...  1991-05-25     210588.0    \n",
       "4             4  ELECTRICIAN    ...  1994-10-22      89194.0    \n",
       "...         ...          ...    ...         ...          ...    \n",
       "1995       1995  POLICE O...    ...  2015-06-09     199596.0    \n",
       "1996       1996  COMMUNIC...    ...  2013-10-06     210588.0    \n",
       "1997       1997  POLICE O...    ...  2015-10-13     199596.0    \n",
       "1998       1998  POLICE O...    ...  2011-07-02     199596.0    \n",
       "1999       1999  FIRE FIG...    ...  2010-07-12     210588.0    \n",
       "\n",
       "[2000 rows x 11 columns]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(employee\n",
    "    .merge(max_sal.rename('MAX_DEPT_SALARY'),\n",
    "           how='left', left_on='DEPARTMENT',\n",
    "           right_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Highlighting the maximum value from each column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CITY                   object\n",
       "STABBR                 object\n",
       "HBCU                  float64\n",
       "MENONLY               float64\n",
       "WOMENONLY             float64\n",
       "                       ...   \n",
       "PCTPELL               float64\n",
       "PCTFLOAN              float64\n",
       "UG25ABV               float64\n",
       "MD_EARN_WNE_P10        object\n",
       "GRAD_DEBT_MDN_SUPP     object\n",
       "Length: 26, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college = pd.read_csv('data/college.csv', index_col='INSTNM')\n",
    "college.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "INSTNM\n",
       "Career Point College                                      20700\n",
       "Ner Israel Rabbinical College                       PrivacyS...\n",
       "Reflections Academy of Beauty                               NaN\n",
       "Capital Area Technical College                            26400\n",
       "West Virginia University Institute of Technology          43400\n",
       "Mid-State Technical College                               32000\n",
       "Strayer University-Huntsville Campus                      49200\n",
       "National Aviation Academy of Tampa Bay                    45000\n",
       "University of California-Santa Cruz                       43000\n",
       "Lexington Theological Seminary                              NaN\n",
       "Name: MD_EARN_WNE_P10, dtype: object"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college.MD_EARN_WNE_P10.sample(10, random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "INSTNM\n",
       "Career Point College                                      14977\n",
       "Ner Israel Rabbinical College                       PrivacyS...\n",
       "Reflections Academy of Beauty                       PrivacyS...\n",
       "Capital Area Technical College                      PrivacyS...\n",
       "West Virginia University Institute of Technology          23969\n",
       "Mid-State Technical College                                8025\n",
       "Strayer University-Huntsville Campus                    36173.5\n",
       "National Aviation Academy of Tampa Bay                    22778\n",
       "University of California-Santa Cruz                       19884\n",
       "Lexington Theological Seminary                      PrivacyS...\n",
       "Name: GRAD_DEBT_MDN_SUPP, dtype: object"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college.GRAD_DEBT_MDN_SUPP.sample(10, random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PrivacySuppressed    822\n",
       "38800                151\n",
       "21500                 97\n",
       "49200                 78\n",
       "27400                 46\n",
       "                    ... \n",
       "68200                  1\n",
       "51300                  1\n",
       "54700                  1\n",
       "83800                  1\n",
       "54400                  1\n",
       "Name: MD_EARN_WNE_P10, Length: 598, dtype: int64"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college.MD_EARN_WNE_P10.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{float, str}"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(college.MD_EARN_WNE_P10.apply(type))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PrivacySuppressed    1510\n",
       "9500                  514\n",
       "27000                 306\n",
       "25827.5               136\n",
       "25000                 124\n",
       "                     ... \n",
       "23185                   1\n",
       "12908.5                 1\n",
       "10100                   1\n",
       "6200                    1\n",
       "12112                   1\n",
       "Name: GRAD_DEBT_MDN_SUPP, Length: 2038, dtype: int64"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college.GRAD_DEBT_MDN_SUPP.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "cols = ['MD_EARN_WNE_P10', 'GRAD_DEBT_MDN_SUPP']\n",
    "for col in cols:\n",
    "    college[col] = pd.to_numeric(college[col], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MD_EARN_WNE_P10       float64\n",
       "GRAD_DEBT_MDN_SUPP    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college.dtypes.loc[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>HBCU</th>\n",
       "      <th>MENONLY</th>\n",
       "      <th>...</th>\n",
       "      <th>PCTFLOAN</th>\n",
       "      <th>UG25ABV</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INSTNM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alabama A &amp; M University</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.8284</td>\n",
       "      <td>0.1049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama at Birmingham</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5214</td>\n",
       "      <td>0.2422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Amridge University</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.7795</td>\n",
       "      <td>0.8540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama in Huntsville</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.4596</td>\n",
       "      <td>0.2640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alabama State University</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.7554</td>\n",
       "      <td>0.1270</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              HBCU  MENONLY  ...  PCTFLOAN  UG25ABV\n",
       "INSTNM                       ...                   \n",
       "Alabama A...   1.0      0.0  ...    0.8284   0.1049\n",
       "Universit...   0.0      0.0  ...    0.5214   0.2422\n",
       "Amridge U...   0.0      0.0  ...    0.7795   0.8540\n",
       "Universit...   0.0      0.0  ...    0.4596   0.2640\n",
       "Alabama S...   1.0      0.0  ...    0.7554   0.1270\n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college_n = college.select_dtypes('number')\n",
    "college_n.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "HBCU          True\n",
       "MENONLY       True\n",
       "WOMENONLY     True\n",
       "RELAFFIL      True\n",
       "SATVRMID     False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "binary_only = college_n.nunique() == 2\n",
    "binary_only.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['HBCU', 'MENONLY', 'WOMENONLY', 'RELAFFIL', 'DISTANCEONLY', 'CURROPER']"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "binary_cols = binary_only[binary_only].index.tolist()\n",
    "binary_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SATVRMID</th>\n",
       "      <th>SATMTMID</th>\n",
       "      <th>...</th>\n",
       "      <th>PCTFLOAN</th>\n",
       "      <th>UG25ABV</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INSTNM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alabama A &amp; M University</th>\n",
       "      <td>424.0</td>\n",
       "      <td>420.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.8284</td>\n",
       "      <td>0.1049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama at Birmingham</th>\n",
       "      <td>570.0</td>\n",
       "      <td>565.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.5214</td>\n",
       "      <td>0.2422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Amridge University</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.7795</td>\n",
       "      <td>0.8540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama in Huntsville</th>\n",
       "      <td>595.0</td>\n",
       "      <td>590.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.4596</td>\n",
       "      <td>0.2640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alabama State University</th>\n",
       "      <td>425.0</td>\n",
       "      <td>430.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.7554</td>\n",
       "      <td>0.1270</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              SATVRMID  SATMTMID  ...  PCTFLOAN  UG25ABV\n",
       "INSTNM                            ...                   \n",
       "Alabama A...     424.0     420.0  ...    0.8284   0.1049\n",
       "Universit...     570.0     565.0  ...    0.5214   0.2422\n",
       "Amridge U...       NaN       NaN  ...    0.7795   0.8540\n",
       "Universit...     595.0     590.0  ...    0.4596   0.2640\n",
       "Alabama S...     425.0     430.0  ...    0.7554   0.1270\n",
       "\n",
       "[5 rows x 16 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college_n2 = college_n.drop(columns=binary_cols)\n",
    "college_n2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SATVRMID      Californ...\n",
       "SATMTMID      Californ...\n",
       "UGDS          Universi...\n",
       "UGDS_WHITE    Mr Leon'...\n",
       "UGDS_BLACK    Velvatex...\n",
       "                 ...     \n",
       "UGDS_UNKN     Le Cordo...\n",
       "PPTUG_EF      Thunderb...\n",
       "PCTPELL       MTI Busi...\n",
       "PCTFLOAN      ABC Beau...\n",
       "UG25ABV       Dongguk ...\n",
       "Length: 16, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "max_cols = college_n2.idxmax()\n",
    "max_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['California Institute of Technology',\n",
       "       'University of Phoenix-Arizona',\n",
       "       \"Mr Leon's School of Hair Design-Moscow\",\n",
       "       'Velvatex College of Beauty Culture',\n",
       "       'Thunderbird School of Global Management'], dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_max_cols = max_cols.unique()\n",
    "unique_max_cols[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SATVRMID</th>\n",
       "      <th>SATMTMID</th>\n",
       "      <th>...</th>\n",
       "      <th>MD_EARN_WNE_P10</th>\n",
       "      <th>GRAD_DEBT_MDN_SUPP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INSTNM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>California Institute of Technology</th>\n",
       "      <td>765.0</td>\n",
       "      <td>785.0</td>\n",
       "      <td>...</td>\n",
       "      <td>77800.0</td>\n",
       "      <td>11812.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Phoenix-Arizona</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mr Leon's School of Hair Design-Moscow</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15710.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Velvatex College of Beauty Culture</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thunderbird School of Global Management</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>118900.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MTI Business College Inc</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>23000.0</td>\n",
       "      <td>9500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABC Beauty College Inc</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dongguk University-Los Angeles</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medical College of Wisconsin</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>233100.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southwest University of Visual Arts-Tucson</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>27200.0</td>\n",
       "      <td>49750.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              SATVRMID  SATMTMID  ...  MD_EARN_WNE_P10  GRAD_DEBT_MDN_SUPP\n",
       "INSTNM                            ...                                     \n",
       "Californi...     765.0     785.0  ...      77800.0          11812.5       \n",
       "Universit...       NaN       NaN  ...          NaN          33000.0       \n",
       "Mr Leon's...       NaN       NaN  ...          NaN          15710.0       \n",
       "Velvatex ...       NaN       NaN  ...          NaN              NaN       \n",
       "Thunderbi...       NaN       NaN  ...     118900.0              NaN       \n",
       "...                ...       ...  ...          ...              ...       \n",
       "MTI Busin...       NaN       NaN  ...      23000.0           9500.0       \n",
       "ABC Beaut...       NaN       NaN  ...          NaN          16500.0       \n",
       "Dongguk U...       NaN       NaN  ...          NaN              NaN       \n",
       "Medical C...       NaN       NaN  ...     233100.0              NaN       \n",
       "Southwest...       NaN       NaN  ...      27200.0          49750.0       \n",
       "\n",
       "[16 rows x 18 columns]"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college_n2.loc[unique_max_cols] #.style.highlight_max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col0 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col2 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col3 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col4 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col5 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col12 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col6 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col7 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col8 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col9 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col10 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col11 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col13 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col14 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col14 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col15 {\n",
       "            background-color:  yellow;\n",
       "        }</style><table id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33f\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >SATVRMID</th>        <th class=\"col_heading level0 col1\" >SATMTMID</th>        <th class=\"col_heading level0 col2\" >UGDS</th>        <th class=\"col_heading level0 col3\" >UGDS_WHITE</th>        <th class=\"col_heading level0 col4\" >UGDS_BLACK</th>        <th class=\"col_heading level0 col5\" >UGDS_HISP</th>        <th class=\"col_heading level0 col6\" >UGDS_ASIAN</th>        <th class=\"col_heading level0 col7\" >UGDS_AIAN</th>        <th class=\"col_heading level0 col8\" >UGDS_NHPI</th>        <th class=\"col_heading level0 col9\" >UGDS_2MOR</th>        <th class=\"col_heading level0 col10\" >UGDS_NRA</th>        <th class=\"col_heading level0 col11\" >UGDS_UNKN</th>        <th class=\"col_heading level0 col12\" >PPTUG_EF</th>        <th class=\"col_heading level0 col13\" >PCTPELL</th>        <th class=\"col_heading level0 col14\" >PCTFLOAN</th>        <th class=\"col_heading level0 col15\" >UG25ABV</th>    </tr>    <tr>        <th class=\"index_name level0\" >INSTNM</th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row0\" class=\"row_heading level0 row0\" >California Institute of Technology</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col0\" class=\"data row0 col0\" >765</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col1\" class=\"data row0 col1\" >785</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col2\" class=\"data row0 col2\" >983</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col3\" class=\"data row0 col3\" >0.2787</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col4\" class=\"data row0 col4\" >0.0153</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col5\" class=\"data row0 col5\" >0.1221</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col6\" class=\"data row0 col6\" >0.4385</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col7\" class=\"data row0 col7\" >0.001</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col8\" class=\"data row0 col8\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col9\" class=\"data row0 col9\" >0.057</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col10\" class=\"data row0 col10\" >0.0875</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col11\" class=\"data row0 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col12\" class=\"data row0 col12\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col13\" class=\"data row0 col13\" >0.1126</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col14\" class=\"data row0 col14\" >0.2303</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow0_col15\" class=\"data row0 col15\" >0.0082</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row1\" class=\"row_heading level0 row1\" >University of Phoenix-Arizona</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col0\" class=\"data row1 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col1\" class=\"data row1 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col2\" class=\"data row1 col2\" >151558</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col3\" class=\"data row1 col3\" >0.3098</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col4\" class=\"data row1 col4\" >0.1555</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col5\" class=\"data row1 col5\" >0.076</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col6\" class=\"data row1 col6\" >0.0082</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col7\" class=\"data row1 col7\" >0.0042</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col8\" class=\"data row1 col8\" >0.005</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col9\" class=\"data row1 col9\" >0.1131</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col10\" class=\"data row1 col10\" >0.0131</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col11\" class=\"data row1 col11\" >0.3152</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col12\" class=\"data row1 col12\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col13\" class=\"data row1 col13\" >0.6009</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col14\" class=\"data row1 col14\" >0.592</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow1_col15\" class=\"data row1 col15\" >nan</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row2\" class=\"row_heading level0 row2\" >Mr Leon's School of Hair Design-Moscow</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col0\" class=\"data row2 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col1\" class=\"data row2 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col2\" class=\"data row2 col2\" >16</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col3\" class=\"data row2 col3\" >1</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col4\" class=\"data row2 col4\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col5\" class=\"data row2 col5\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col6\" class=\"data row2 col6\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col7\" class=\"data row2 col7\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col8\" class=\"data row2 col8\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col9\" class=\"data row2 col9\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col10\" class=\"data row2 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col11\" class=\"data row2 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col12\" class=\"data row2 col12\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col13\" class=\"data row2 col13\" >0.625</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col14\" class=\"data row2 col14\" >0.625</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow2_col15\" class=\"data row2 col15\" >0.2</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row3\" class=\"row_heading level0 row3\" >Velvatex College of Beauty Culture</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col0\" class=\"data row3 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col1\" class=\"data row3 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col2\" class=\"data row3 col2\" >25</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col3\" class=\"data row3 col3\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col4\" class=\"data row3 col4\" >1</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col5\" class=\"data row3 col5\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col6\" class=\"data row3 col6\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col7\" class=\"data row3 col7\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col8\" class=\"data row3 col8\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col9\" class=\"data row3 col9\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col10\" class=\"data row3 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col11\" class=\"data row3 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col12\" class=\"data row3 col12\" >0.2</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col13\" class=\"data row3 col13\" >0.7692</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col14\" class=\"data row3 col14\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow3_col15\" class=\"data row3 col15\" >0.52</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row4\" class=\"row_heading level0 row4\" >Thunderbird School of Global Management</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col0\" class=\"data row4 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col1\" class=\"data row4 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col2\" class=\"data row4 col2\" >1</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col3\" class=\"data row4 col3\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col4\" class=\"data row4 col4\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col5\" class=\"data row4 col5\" >1</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col6\" class=\"data row4 col6\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col7\" class=\"data row4 col7\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col8\" class=\"data row4 col8\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col9\" class=\"data row4 col9\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col10\" class=\"data row4 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col11\" class=\"data row4 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col12\" class=\"data row4 col12\" >1</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col13\" class=\"data row4 col13\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col14\" class=\"data row4 col14\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow4_col15\" class=\"data row4 col15\" >0</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row5\" class=\"row_heading level0 row5\" >Cosmopolitan Beauty and Tech School</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col0\" class=\"data row5 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col1\" class=\"data row5 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col2\" class=\"data row5 col2\" >110</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col3\" class=\"data row5 col3\" >0.0091</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col4\" class=\"data row5 col4\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col5\" class=\"data row5 col5\" >0.0182</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col6\" class=\"data row5 col6\" >0.9727</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col7\" class=\"data row5 col7\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col8\" class=\"data row5 col8\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col9\" class=\"data row5 col9\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col10\" class=\"data row5 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col11\" class=\"data row5 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col12\" class=\"data row5 col12\" >0.3182</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col13\" class=\"data row5 col13\" >0.7761</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col14\" class=\"data row5 col14\" >0.1244</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow5_col15\" class=\"data row5 col15\" >0.9545</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row6\" class=\"row_heading level0 row6\" >Haskell Indian Nations University</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col0\" class=\"data row6 col0\" >430</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col1\" class=\"data row6 col1\" >440</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col2\" class=\"data row6 col2\" >805</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col3\" class=\"data row6 col3\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col4\" class=\"data row6 col4\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col5\" class=\"data row6 col5\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col6\" class=\"data row6 col6\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col7\" class=\"data row6 col7\" >1</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col8\" class=\"data row6 col8\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col9\" class=\"data row6 col9\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col10\" class=\"data row6 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col11\" class=\"data row6 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col12\" class=\"data row6 col12\" >0.0224</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col13\" class=\"data row6 col13\" >0.8396</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col14\" class=\"data row6 col14\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow6_col15\" class=\"data row6 col15\" >0.2089</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row7\" class=\"row_heading level0 row7\" >Palau Community College</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col0\" class=\"data row7 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col1\" class=\"data row7 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col2\" class=\"data row7 col2\" >602</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col3\" class=\"data row7 col3\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col4\" class=\"data row7 col4\" >0.0017</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col5\" class=\"data row7 col5\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col6\" class=\"data row7 col6\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col7\" class=\"data row7 col7\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col8\" class=\"data row7 col8\" >0.9983</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col9\" class=\"data row7 col9\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col10\" class=\"data row7 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col11\" class=\"data row7 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col12\" class=\"data row7 col12\" >0.3887</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col13\" class=\"data row7 col13\" >0.856</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col14\" class=\"data row7 col14\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow7_col15\" class=\"data row7 col15\" >0.2616</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row8\" class=\"row_heading level0 row8\" >LIU Brentwood</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col0\" class=\"data row8 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col1\" class=\"data row8 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col2\" class=\"data row8 col2\" >15</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col3\" class=\"data row8 col3\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col4\" class=\"data row8 col4\" >0.1333</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col5\" class=\"data row8 col5\" >0.2667</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col6\" class=\"data row8 col6\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col7\" class=\"data row8 col7\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col8\" class=\"data row8 col8\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col9\" class=\"data row8 col9\" >0.5333</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col10\" class=\"data row8 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col11\" class=\"data row8 col11\" >0.0667</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col12\" class=\"data row8 col12\" >0.4</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col13\" class=\"data row8 col13\" >0.5652</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col14\" class=\"data row8 col14\" >0.7826</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow8_col15\" class=\"data row8 col15\" >0.7826</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row9\" class=\"row_heading level0 row9\" >California University of Management and Sciences</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col0\" class=\"data row9 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col1\" class=\"data row9 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col2\" class=\"data row9 col2\" >98</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col3\" class=\"data row9 col3\" >0.0102</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col4\" class=\"data row9 col4\" >0.0204</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col5\" class=\"data row9 col5\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col6\" class=\"data row9 col6\" >0.0408</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col7\" class=\"data row9 col7\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col8\" class=\"data row9 col8\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col9\" class=\"data row9 col9\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col10\" class=\"data row9 col10\" >0.9286</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col11\" class=\"data row9 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col12\" class=\"data row9 col12\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col13\" class=\"data row9 col13\" >0.0926</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col14\" class=\"data row9 col14\" >0.0556</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow9_col15\" class=\"data row9 col15\" >0.6852</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row10\" class=\"row_heading level0 row10\" >Le Cordon Bleu College of Culinary Arts-San Francisco</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col0\" class=\"data row10 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col1\" class=\"data row10 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col2\" class=\"data row10 col2\" >442</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col3\" class=\"data row10 col3\" >0.0317</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col4\" class=\"data row10 col4\" >0.009</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col5\" class=\"data row10 col5\" >0.0113</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col6\" class=\"data row10 col6\" >0.0271</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col7\" class=\"data row10 col7\" >0.0045</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col8\" class=\"data row10 col8\" >0.0023</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col9\" class=\"data row10 col9\" >0.0113</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col10\" class=\"data row10 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col11\" class=\"data row10 col11\" >0.9027</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col12\" class=\"data row10 col12\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col13\" class=\"data row10 col13\" >0.3722</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col14\" class=\"data row10 col14\" >0.5358</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow10_col15\" class=\"data row10 col15\" >0.544</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row11\" class=\"row_heading level0 row11\" >MTI Business College Inc</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col0\" class=\"data row11 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col1\" class=\"data row11 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col2\" class=\"data row11 col2\" >146</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col3\" class=\"data row11 col3\" >0.2397</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col4\" class=\"data row11 col4\" >0.5137</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col5\" class=\"data row11 col5\" >0.1438</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col6\" class=\"data row11 col6\" >0.0479</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col7\" class=\"data row11 col7\" >0.0342</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col8\" class=\"data row11 col8\" >0.0205</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col9\" class=\"data row11 col9\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col10\" class=\"data row11 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col11\" class=\"data row11 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col12\" class=\"data row11 col12\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col13\" class=\"data row11 col13\" >1</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col14\" class=\"data row11 col14\" >1</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow11_col15\" class=\"data row11 col15\" >0.3986</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row12\" class=\"row_heading level0 row12\" >ABC Beauty College Inc</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col0\" class=\"data row12 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col1\" class=\"data row12 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col2\" class=\"data row12 col2\" >38</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col3\" class=\"data row12 col3\" >0.2895</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col4\" class=\"data row12 col4\" >0.6579</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col5\" class=\"data row12 col5\" >0.0526</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col6\" class=\"data row12 col6\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col7\" class=\"data row12 col7\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col8\" class=\"data row12 col8\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col9\" class=\"data row12 col9\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col10\" class=\"data row12 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col11\" class=\"data row12 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col12\" class=\"data row12 col12\" >0.2105</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col13\" class=\"data row12 col13\" >0.9815</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col14\" class=\"data row12 col14\" >1</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow12_col15\" class=\"data row12 col15\" >0.4688</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33flevel0_row13\" class=\"row_heading level0 row13\" >Dongguk University-Los Angeles</th>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col0\" class=\"data row13 col0\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col1\" class=\"data row13 col1\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col2\" class=\"data row13 col2\" >20</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col3\" class=\"data row13 col3\" >0.35</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col4\" class=\"data row13 col4\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col5\" class=\"data row13 col5\" >0.15</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col6\" class=\"data row13 col6\" >0.5</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col7\" class=\"data row13 col7\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col8\" class=\"data row13 col8\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col9\" class=\"data row13 col9\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col10\" class=\"data row13 col10\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col11\" class=\"data row13 col11\" >0</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col12\" class=\"data row13 col12\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col13\" class=\"data row13 col13\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col14\" class=\"data row13 col14\" >nan</td>\n",
       "                        <td id=\"T_dc250614_2208_11ea_8a01_a45e60ecc33frow13_col15\" class=\"data row13 col15\" >1</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x115ae07f0>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college_n2.loc[unique_max_cols].style.highlight_max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_binary_cols(df):\n",
    "    binary_only = df.nunique() == 2\n",
    "    cols = binary_only[binary_only].index.tolist()\n",
    "    return df.drop(columns=cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "def select_rows_with_max_cols(df):\n",
    "    max_cols = df.idxmax()\n",
    "    unique = max_cols.unique()\n",
    "    return df.loc[unique]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SATVRMID</th>\n",
       "      <th>SATMTMID</th>\n",
       "      <th>...</th>\n",
       "      <th>MD_EARN_WNE_P10</th>\n",
       "      <th>GRAD_DEBT_MDN_SUPP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INSTNM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>California Institute of Technology</th>\n",
       "      <td>765.0</td>\n",
       "      <td>785.0</td>\n",
       "      <td>...</td>\n",
       "      <td>77800.0</td>\n",
       "      <td>11812.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Phoenix-Arizona</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mr Leon's School of Hair Design-Moscow</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15710.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Velvatex College of Beauty Culture</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Thunderbird School of Global Management</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>118900.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MTI Business College Inc</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>23000.0</td>\n",
       "      <td>9500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ABC Beauty College Inc</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dongguk University-Los Angeles</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Medical College of Wisconsin</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>233100.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southwest University of Visual Arts-Tucson</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>27200.0</td>\n",
       "      <td>49750.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              SATVRMID  SATMTMID  ...  MD_EARN_WNE_P10  GRAD_DEBT_MDN_SUPP\n",
       "INSTNM                            ...                                     \n",
       "Californi...     765.0     785.0  ...      77800.0          11812.5       \n",
       "Universit...       NaN       NaN  ...          NaN          33000.0       \n",
       "Mr Leon's...       NaN       NaN  ...          NaN          15710.0       \n",
       "Velvatex ...       NaN       NaN  ...          NaN              NaN       \n",
       "Thunderbi...       NaN       NaN  ...     118900.0              NaN       \n",
       "...                ...       ...  ...          ...              ...       \n",
       "MTI Busin...       NaN       NaN  ...      23000.0           9500.0       \n",
       "ABC Beaut...       NaN       NaN  ...          NaN          16500.0       \n",
       "Dongguk U...       NaN       NaN  ...          NaN              NaN       \n",
       "Medical C...       NaN       NaN  ...     233100.0              NaN       \n",
       "Southwest...       NaN       NaN  ...      27200.0          49750.0       \n",
       "\n",
       "[16 rows x 18 columns]"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(college\n",
    "   .assign(\n",
    "       MD_EARN_WNE_P10=pd.to_numeric(college.MD_EARN_WNE_P10, errors='coerce'),\n",
    "       GRAD_DEBT_MDN_SUPP=pd.to_numeric(college.GRAD_DEBT_MDN_SUPP, errors='coerce'))\n",
    "   .select_dtypes('number')\n",
    "   .pipe(remove_binary_cols)\n",
    "   .pipe(select_rows_with_max_cols)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There's more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "college = pd.read_csv('data/college.csv', index_col='INSTNM')\n",
    "college_ugds = college.filter(like='UGDS_').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "    #T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow0_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow1_col0 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow2_col1 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow3_col0 {\n",
       "            background-color:  yellow;\n",
       "        }    #T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow4_col1 {\n",
       "            background-color:  yellow;\n",
       "        }</style><table id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33f\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >UGDS_WHITE</th>        <th class=\"col_heading level0 col1\" >UGDS_BLACK</th>        <th class=\"col_heading level0 col2\" >UGDS_HISP</th>        <th class=\"col_heading level0 col3\" >UGDS_ASIAN</th>        <th class=\"col_heading level0 col4\" >UGDS_AIAN</th>        <th class=\"col_heading level0 col5\" >UGDS_NHPI</th>        <th class=\"col_heading level0 col6\" >UGDS_2MOR</th>        <th class=\"col_heading level0 col7\" >UGDS_NRA</th>        <th class=\"col_heading level0 col8\" >UGDS_UNKN</th>    </tr>    <tr>        <th class=\"index_name level0\" >INSTNM</th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>        <th class=\"blank\" ></th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33flevel0_row0\" class=\"row_heading level0 row0\" >Alabama A & M University</th>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow0_col0\" class=\"data row0 col0\" >0.0333</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow0_col1\" class=\"data row0 col1\" >0.9353</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow0_col2\" class=\"data row0 col2\" >0.0055</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow0_col3\" class=\"data row0 col3\" >0.0019</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow0_col4\" class=\"data row0 col4\" >0.0024</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow0_col5\" class=\"data row0 col5\" >0.0019</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow0_col6\" class=\"data row0 col6\" >0</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow0_col7\" class=\"data row0 col7\" >0.0059</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow0_col8\" class=\"data row0 col8\" >0.0138</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33flevel0_row1\" class=\"row_heading level0 row1\" >University of Alabama at Birmingham</th>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow1_col0\" class=\"data row1 col0\" >0.5922</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow1_col1\" class=\"data row1 col1\" >0.26</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow1_col2\" class=\"data row1 col2\" >0.0283</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow1_col3\" class=\"data row1 col3\" >0.0518</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow1_col4\" class=\"data row1 col4\" >0.0022</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow1_col5\" class=\"data row1 col5\" >0.0007</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow1_col6\" class=\"data row1 col6\" >0.0368</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow1_col7\" class=\"data row1 col7\" >0.0179</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow1_col8\" class=\"data row1 col8\" >0.01</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33flevel0_row2\" class=\"row_heading level0 row2\" >Amridge University</th>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow2_col0\" class=\"data row2 col0\" >0.299</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow2_col1\" class=\"data row2 col1\" >0.4192</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow2_col2\" class=\"data row2 col2\" >0.0069</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow2_col3\" class=\"data row2 col3\" >0.0034</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow2_col4\" class=\"data row2 col4\" >0</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow2_col5\" class=\"data row2 col5\" >0</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow2_col6\" class=\"data row2 col6\" >0</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow2_col7\" class=\"data row2 col7\" >0</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow2_col8\" class=\"data row2 col8\" >0.2715</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33flevel0_row3\" class=\"row_heading level0 row3\" >University of Alabama in Huntsville</th>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow3_col0\" class=\"data row3 col0\" >0.6988</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow3_col1\" class=\"data row3 col1\" >0.1255</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow3_col2\" class=\"data row3 col2\" >0.0382</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow3_col3\" class=\"data row3 col3\" >0.0376</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow3_col4\" class=\"data row3 col4\" >0.0143</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow3_col5\" class=\"data row3 col5\" >0.0002</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow3_col6\" class=\"data row3 col6\" >0.0172</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow3_col7\" class=\"data row3 col7\" >0.0332</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow3_col8\" class=\"data row3 col8\" >0.035</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33flevel0_row4\" class=\"row_heading level0 row4\" >Alabama State University</th>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow4_col0\" class=\"data row4 col0\" >0.0158</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow4_col1\" class=\"data row4 col1\" >0.9208</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow4_col2\" class=\"data row4 col2\" >0.0121</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow4_col3\" class=\"data row4 col3\" >0.0019</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow4_col4\" class=\"data row4 col4\" >0.001</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow4_col5\" class=\"data row4 col5\" >0.0006</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow4_col6\" class=\"data row4 col6\" >0.0098</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow4_col7\" class=\"data row4 col7\" >0.0243</td>\n",
       "                        <td id=\"T_45eea0d2_2209_11ea_b7a0_a45e60ecc33frow4_col8\" class=\"data row4 col8\" >0.0137</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x114bb7ba8>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college_ugds.style.highlight_max(axis='columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Replicating idxmax with method chaining"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_binary_cols(df):\n",
    "    binary_only = df.nunique() == 2\n",
    "    cols = binary_only[binary_only].index.tolist()\n",
    "    return df.drop(columns=cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "college_n = (college\n",
    "   .assign(\n",
    "       MD_EARN_WNE_P10=pd.to_numeric(college.MD_EARN_WNE_P10, errors='coerce'),\n",
    "       GRAD_DEBT_MDN_SUPP=pd.to_numeric(college.GRAD_DEBT_MDN_SUPP, errors='coerce'))\n",
    "   .select_dtypes('number')\n",
    "   .pipe(remove_binary_cols)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SATVRMID         765.0\n",
       "SATMTMID         785.0\n",
       "UGDS          151558.0\n",
       "UGDS_WHITE         1.0\n",
       "UGDS_BLACK         1.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college_n.max().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SATVRMID</th>\n",
       "      <th>SATMTMID</th>\n",
       "      <th>...</th>\n",
       "      <th>MD_EARN_WNE_P10</th>\n",
       "      <th>GRAD_DEBT_MDN_SUPP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INSTNM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alabama A &amp; M University</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama at Birmingham</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Amridge University</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama in Huntsville</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alabama State University</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              SATVRMID  SATMTMID  ...  MD_EARN_WNE_P10  GRAD_DEBT_MDN_SUPP\n",
       "INSTNM                            ...                                     \n",
       "Alabama A...     False     False  ...        False            False       \n",
       "Universit...     False     False  ...        False            False       \n",
       "Amridge U...     False     False  ...        False            False       \n",
       "Universit...     False     False  ...        False            False       \n",
       "Alabama S...     False     False  ...        False            False       \n",
       "\n",
       "[5 rows x 18 columns]"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college_n.eq(college_n.max()).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "INSTNM\n",
       "Alabama A & M University               False\n",
       "University of Alabama at Birmingham    False\n",
       "Amridge University                     False\n",
       "University of Alabama in Huntsville    False\n",
       "Alabama State University               False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "has_row_max = (college_n\n",
    "    .eq(college_n.max())\n",
    "    .any(axis='columns')\n",
    ")\n",
    "has_row_max.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7535, 18)"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college_n.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "401"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "has_row_max.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SATVRMID</th>\n",
       "      <th>SATMTMID</th>\n",
       "      <th>...</th>\n",
       "      <th>MD_EARN_WNE_P10</th>\n",
       "      <th>GRAD_DEBT_MDN_SUPP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INSTNM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alabama A &amp; M University</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama at Birmingham</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Amridge University</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama in Huntsville</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alabama State University</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SAE Institute of Technology  San Francisco</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rasmussen College - Overland Park</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>National Personal Training Institute of Cleveland</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bay Area Medical Academy - San Jose Satellite Location</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Excel Learning Center-San Antonio South</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7535 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              SATVRMID  SATMTMID  ...  MD_EARN_WNE_P10  GRAD_DEBT_MDN_SUPP\n",
       "INSTNM                            ...                                     \n",
       "Alabama A...         0         0  ...            0                0       \n",
       "Universit...         0         0  ...            0                0       \n",
       "Amridge U...         0         0  ...            0                0       \n",
       "Universit...         0         0  ...            0                0       \n",
       "Alabama S...         0         0  ...            0                0       \n",
       "...                ...       ...  ...          ...              ...       \n",
       "SAE Insti...         1         1  ...            1                2       \n",
       "Rasmussen...         1         1  ...            1                2       \n",
       "National ...         1         1  ...            1                2       \n",
       "Bay Area ...         1         1  ...            1                2       \n",
       "Excel Lea...         1         1  ...            1                2       \n",
       "\n",
       "[7535 rows x 18 columns]"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college_n.eq(college_n.max()).cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SATVRMID</th>\n",
       "      <th>SATMTMID</th>\n",
       "      <th>...</th>\n",
       "      <th>MD_EARN_WNE_P10</th>\n",
       "      <th>GRAD_DEBT_MDN_SUPP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INSTNM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alabama A &amp; M University</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama at Birmingham</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Amridge University</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama in Huntsville</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alabama State University</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SAE Institute of Technology  San Francisco</th>\n",
       "      <td>7305</td>\n",
       "      <td>7305</td>\n",
       "      <td>...</td>\n",
       "      <td>3445</td>\n",
       "      <td>10266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rasmussen College - Overland Park</th>\n",
       "      <td>7306</td>\n",
       "      <td>7306</td>\n",
       "      <td>...</td>\n",
       "      <td>3446</td>\n",
       "      <td>10268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>National Personal Training Institute of Cleveland</th>\n",
       "      <td>7307</td>\n",
       "      <td>7307</td>\n",
       "      <td>...</td>\n",
       "      <td>3447</td>\n",
       "      <td>10270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bay Area Medical Academy - San Jose Satellite Location</th>\n",
       "      <td>7308</td>\n",
       "      <td>7308</td>\n",
       "      <td>...</td>\n",
       "      <td>3448</td>\n",
       "      <td>10272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Excel Learning Center-San Antonio South</th>\n",
       "      <td>7309</td>\n",
       "      <td>7309</td>\n",
       "      <td>...</td>\n",
       "      <td>3449</td>\n",
       "      <td>10274</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7535 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              SATVRMID  SATMTMID  ...  MD_EARN_WNE_P10  GRAD_DEBT_MDN_SUPP\n",
       "INSTNM                            ...                                     \n",
       "Alabama A...         0         0  ...            0                0       \n",
       "Universit...         0         0  ...            0                0       \n",
       "Amridge U...         0         0  ...            0                0       \n",
       "Universit...         0         0  ...            0                0       \n",
       "Alabama S...         0         0  ...            0                0       \n",
       "...                ...       ...  ...          ...              ...       \n",
       "SAE Insti...      7305      7305  ...         3445            10266       \n",
       "Rasmussen...      7306      7306  ...         3446            10268       \n",
       "National ...      7307      7307  ...         3447            10270       \n",
       "Bay Area ...      7308      7308  ...         3448            10272       \n",
       "Excel Lea...      7309      7309  ...         3449            10274       \n",
       "\n",
       "[7535 rows x 18 columns]"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(college_n\n",
    "    .eq(college_n.max())\n",
    "    .cumsum()\n",
    "    .cumsum()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "has_row_max2 = (college_n\n",
    "    .eq(college_n.max()) \n",
    "    .cumsum() \n",
    "    .cumsum() \n",
    "    .eq(1) \n",
    "    .any(axis='columns')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "INSTNM\n",
       "Alabama A & M University               False\n",
       "University of Alabama at Birmingham    False\n",
       "Amridge University                     False\n",
       "University of Alabama in Huntsville    False\n",
       "Alabama State University               False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "has_row_max2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "has_row_max2.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Thunderbird School of Global Management',\n",
       "       'Southwest University of Visual Arts-Tucson', 'ABC Beauty College Inc',\n",
       "       'Velvatex College of Beauty Culture',\n",
       "       'California Institute of Technology',\n",
       "       'Le Cordon Bleu College of Culinary Arts-San Francisco',\n",
       "       'MTI Business College Inc', 'Dongguk University-Los Angeles',\n",
       "       'Mr Leon's School of Hair Design-Moscow',\n",
       "       'Haskell Indian Nations University', 'LIU Brentwood',\n",
       "       'Medical College of Wisconsin', 'Palau Community College',\n",
       "       'California University of Management and Sciences',\n",
       "       'Cosmopolitan Beauty and Tech School', 'University of Phoenix-Arizona'],\n",
       "      dtype='object', name='INSTNM')"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idxmax_cols = has_row_max2[has_row_max2].index\n",
    "idxmax_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(college_n.idxmax().unique()) == set(idxmax_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "def idx_max(df):\n",
    "     has_row_max = (df\n",
    "         .eq(df.max())\n",
    "         .cumsum()\n",
    "         .cumsum()\n",
    "         .eq(1)\n",
    "         .any(axis='columns')\n",
    "     )\n",
    "     return has_row_max[has_row_max].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Thunderbird School of Global Management',\n",
       "       'Southwest University of Visual Arts-Tucson', 'ABC Beauty College Inc',\n",
       "       'Velvatex College of Beauty Culture',\n",
       "       'California Institute of Technology',\n",
       "       'Le Cordon Bleu College of Culinary Arts-San Francisco',\n",
       "       'MTI Business College Inc', 'Dongguk University-Los Angeles',\n",
       "       'Mr Leon's School of Hair Design-Moscow',\n",
       "       'Haskell Indian Nations University', 'LIU Brentwood',\n",
       "       'Medical College of Wisconsin', 'Palau Community College',\n",
       "       'California University of Management and Sciences',\n",
       "       'Cosmopolitan Beauty and Tech School', 'University of Phoenix-Arizona'],\n",
       "      dtype='object', name='INSTNM')"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx_max(college_n)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There's more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "def idx_max(df):\n",
    "     has_row_max = (df\n",
    "         .eq(df.max())\n",
    "         .cumsum()\n",
    "         .cumsum()\n",
    "         .eq(1)\n",
    "         .any(axis='columns')\n",
    "         [lambda df_: df_]\n",
    "         .index\n",
    "     )\n",
    "     return has_row_max"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.62 ms ± 80.4 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit college_n.idxmax().values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "29.9 ms ± 11.2 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit idx_max(college_n)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Finding the most common maximum of columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UGDS_WHITE</th>\n",
       "      <th>UGDS_BLACK</th>\n",
       "      <th>...</th>\n",
       "      <th>UGDS_NRA</th>\n",
       "      <th>UGDS_UNKN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>INSTNM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alabama A &amp; M University</th>\n",
       "      <td>0.0333</td>\n",
       "      <td>0.9353</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0059</td>\n",
       "      <td>0.0138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama at Birmingham</th>\n",
       "      <td>0.5922</td>\n",
       "      <td>0.2600</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0179</td>\n",
       "      <td>0.0100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Amridge University</th>\n",
       "      <td>0.2990</td>\n",
       "      <td>0.4192</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.2715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>University of Alabama in Huntsville</th>\n",
       "      <td>0.6988</td>\n",
       "      <td>0.1255</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0332</td>\n",
       "      <td>0.0350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alabama State University</th>\n",
       "      <td>0.0158</td>\n",
       "      <td>0.9208</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0243</td>\n",
       "      <td>0.0137</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              UGDS_WHITE  UGDS_BLACK  ...  UGDS_NRA  UGDS_UNKN\n",
       "INSTNM                                ...                     \n",
       "Alabama A...      0.0333      0.9353  ...    0.0059     0.0138\n",
       "Universit...      0.5922      0.2600  ...    0.0179     0.0100\n",
       "Amridge U...      0.2990      0.4192  ...    0.0000     0.2715\n",
       "Universit...      0.6988      0.1255  ...    0.0332     0.0350\n",
       "Alabama S...      0.0158      0.9208  ...    0.0243     0.0137\n",
       "\n",
       "[5 rows x 9 columns]"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "college = pd.read_csv('data/college.csv', index_col='INSTNM')\n",
    "college_ugds = college.filter(like='UGDS_')\n",
    "college_ugds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "INSTNM\n",
       "Alabama A & M University               UGDS_BLACK\n",
       "University of Alabama at Birmingham    UGDS_WHITE\n",
       "Amridge University                     UGDS_BLACK\n",
       "University of Alabama in Huntsville    UGDS_WHITE\n",
       "Alabama State University               UGDS_BLACK\n",
       "dtype: object"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "highest_percentage_race = college_ugds.idxmax(axis='columns')\n",
    "highest_percentage_race.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UGDS_WHITE    0.670352\n",
       "UGDS_BLACK    0.151586\n",
       "UGDS_HISP     0.129473\n",
       "UGDS_UNKN     0.023422\n",
       "UGDS_ASIAN    0.012074\n",
       "UGDS_AIAN     0.006110\n",
       "UGDS_NRA      0.004073\n",
       "UGDS_NHPI     0.001746\n",
       "UGDS_2MOR     0.001164\n",
       "dtype: float64"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "highest_percentage_race.value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There's more..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UGDS_WHITE    0.661228\n",
       "UGDS_HISP     0.230326\n",
       "UGDS_UNKN     0.071977\n",
       "UGDS_NRA      0.018234\n",
       "UGDS_ASIAN    0.009597\n",
       "UGDS_2MOR     0.006718\n",
       "UGDS_AIAN     0.000960\n",
       "UGDS_NHPI     0.000960\n",
       "dtype: float64"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(college_ugds\n",
    "    [highest_percentage_race == 'UGDS_BLACK']\n",
    "    .drop(columns='UGDS_BLACK')\n",
    "    .idxmax(axis='columns')\n",
    "    .value_counts(normalize=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
